package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.AdresseData;
import agenceVoyageVirtuel.client.beans.CarteData;
import agenceVoyageVirtuel.client.beans.ClientData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Carte extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Carte() {
		// nothing to do
	}
	
	public List<CarteData> getCartesUtilisees(ClientData clientCourant) {
		String query = "SELECT t_carte.idCarte, numCarte, dateCarte, cleCarte"
				+ " FROM t_commande JOIN t_carte"
				+ " WHERE idClient=" + clientCourant.getIdClient()
				+ " AND t_commande.idCarte=t_carte.idCarte;";

		// prepare for rpc transport
		List<CarteData> carteDatas = new ArrayList<CarteData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				carteDatas.add(new CarteData());
				carteDatas.get(i).setIdCarte(result.getInt(1));
				carteDatas.get(i).setNumCarte(result.getString(2));
				carteDatas.get(i).setDateCarte(result.getString(3));
				carteDatas.get(i).setCleCarte(result.getString(4));
				
				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return carteDatas;		
	}
	
	private int chercherCarte(CarteData carte) {
		String query = "SELECT idCarte"
				+ " FROM t_carte"
				+ " WHERE numCarte=" + carte.getNumCarte()
				+ " AND dateCarte=" + carte.getDateCarte()
				+ " AND cleCarte=" + carte.getCleCarte()
				+ ";";
	
		// prepare for rpc transport
		int id = -1;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			
			while(result.next()) {
				id = result.getInt(1); //idCarte
				break;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return id;		
	}	
	
	public void creerCarte(CarteData carte) {
		int id = chercherCarte(carte);
		
		if (id != -1) {
			carte.setIdCarte(id);
			return;
		}
		
		String query = "INSERT INTO t_carte (numCarte, dateCarte, cleCarte)"
				+ " VALUES (" + carte.getNumCarte()
				+ ", " + carte.getDateCarte()
				+ ", " + carte.getCleCarte()
				+ ");";
	
		try {
			Connection connection = getConn();
			PreparedStatement  prepareStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			prepareStatement.executeUpdate();
	        ResultSet keys = prepareStatement.getGeneratedKeys();
	        
	        while(keys.next()) {
	        	carte.setIdCarte(keys.getInt(1)); //idCarte
	        	break;
	        }
			
	        keys.close();
	        prepareStatement.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}
		
		return;
	}	
}
